﻿//Copyright Notice:  ©2009 Microsoft Corporation.  All rights reserved.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

[assembly: CLSCompliant(true)]

namespace Microsoft.InformationSecurity.CISF.Security.AuthZServices
{
    /// <summary>
    /// SQL data access object
    /// </summary>
    public static class AuthZDataAccess 
    {
        #region Methods

        #region GetSqlDataReader
        /// <summary>
        /// Executes the provided SQL query and returns a collection of object arrays.
        /// </summary>
        /// <param name="dbKey">Name of the configuration key to use to get database connection string.</param>
        /// <param name="sqlQuery">A SqlQuery object.</param>
        /// <returns></returns>
        public static Collection<object[]> GetSqlDataReader(string dbKey, SqlQuery sqlQuery)
        {
            Database database = null;
            SqlDataReader sqlDataReader = null;
            Collection<object[]> dataRows = new Collection<object[]>();

            try
            {
                database = DatabaseFactory.CreateDatabase(dbKey);
                SqlCommand sqlCmd = new SqlCommand(sqlQuery.CommandText);
                sqlCmd.CommandType = sqlQuery.CommandType;

                foreach(SqlParameter sqlParameter in sqlQuery.SqlParameters)
                    sqlCmd.Parameters.Add(sqlParameter.Name, sqlParameter.Type, sqlParameter.Length).Value = sqlParameter.Value;

                sqlDataReader = (SqlDataReader)database.ExecuteReader(sqlCmd);

                if (sqlDataReader.HasRows)
                {
                    while (sqlDataReader.Read())
                    {
                        object[] values = new object[sqlDataReader.FieldCount];
                        sqlDataReader.GetSqlValues(values);
                        dataRows.Add(values);
                    }
                }
                return dataRows;
            }
            catch (Exception exObject)
            {
                    throw exObject;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();

                if (database != null)
                    database = null;
            }
        }

        #endregion

        #region ExecuteNonQueryTransaction
        /// <summary>
        /// Executes a batch of queries in a single database transaction.
        /// </summary>
        /// <param name="dbKey">Name of the configuration key to use to get database connection string.</param>
        /// <param name="sqlQueries">A collection of SQL queries to execute.</param>
        public static void ExecuteNonQueryTransaction(string dbKey, Collection<SqlQuery> sqlQueries)
        {
            Database database = null;

            try
            {
                database = DatabaseFactory.CreateDatabase(dbKey);
                using (DbConnection connection = database.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();

                    try
                    {
                        SqlCommand sqlCmd = null;
                        foreach (SqlQuery sqlQuery in sqlQueries)
                        {
                            sqlCmd = new SqlCommand(sqlQuery.CommandText);
                            sqlCmd.CommandType = sqlQuery.CommandType;

                            sqlCmd.Parameters.Clear();
                            foreach (SqlParameter sqlParameter in sqlQuery.SqlParameters)
                                sqlCmd.Parameters.Add(sqlParameter.Name, sqlParameter.Type, sqlParameter.Length).Value = sqlParameter.Value;

                            database.ExecuteNonQuery(sqlCmd);
                        }
                        transaction.Commit();
                        connection.Close();
                    }

                    catch (Exception e)
                    {
                        transaction.Rollback();
                        throw e;
                    }
                }
            }
            catch (Exception exObject)
            {
                throw exObject;
            }
            finally
            {
                if (database != null)
                    database = null;
            }
        }

        #endregion

        #region FormatFilter
        /// <summary>
        /// Formats the collection into a string list of filters which can be parsed by the 
        /// ISRM_ftStringToTable function.
        /// </summary>
        /// <param name="filters">A collection of filters.</param>
        /// <returns>Comma delimited string of filters.
        /// </returns>
        public static string FormatFilter(Collection<string> filters)
        {
            string inputFormat = "{0},";
            StringBuilder input = new StringBuilder();
            string filterOut = "";

            if (filters != null)
            {
                foreach (string filter in filters)
                    input.Append(String.Format(inputFormat, filter));

                filterOut = input.ToString();
            }

            return filterOut;
        }

        #endregion

        #endregion
    }
}
